// Project: Salient
// http://salient.codeplex.com
// 
// Copyright 2010, Sky Sanders <sky at skysanders.net>
// Dual licensed under the MIT or GPL Version 2 licenses.
// http://salient.codeplex.com/license
//  
// Date: June 16 2010 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Reflection;
using System.Text.RegularExpressions;

namespace Salient.SQLite
{
    /// <summary>
    /// 
    /// DONE: Added VersionedSQLiteOrmLayer
    /// TODO: explain limitations and behavior very very clearly
    /// 
    /// 
    /// Very simple dynamic Model-First versioned ORM layer.
    /// 
    /// For use in quick deployment low traffic prototyping scenarios.
    /// 
    /// Build some field bags, define the PK and fire up an instance of
    /// VersionedSQLiteOrmLayer. DeadSimple.
    /// 
    /// If shape of domain objects change, simply delete the root file
    /// and the db will be regenerated. There are not facilities for
    /// modifying schema and retaining data yet, and not likely to be
    /// 
    /// There is no concurrency management. Use transactions and be smart.
    /// 
    /// 
    /// Domain object fields must be fields.
    /// 
    /// Limited data type support
    /// 
    /// Non null fields only 
    /// 
    /// Single column integer (long) PK fields
    /// 
    /// PK field is described by a DescriptionAttribute
    /// 
    /// AutoIncrement field MUST be named 'RowID'
    /// 
    /// 
    /// [Description("ID")]
    /// public class Site
    /// {
    ///     public long ID;
    ///     public string Name;
    ///     public DateTime Updated;
    ///     public long TotalUsers;
    ///     public bool Updating;
    ///     public long MinScore;
    ///     public string Title;
    ///     public string Key;
    ///     public long Version;
    /// }
    /// 
    /// [Description("RowID")]
    /// public class User
    /// {
    ///     public long RowID;
    ///     public long SiteId;
    ///     public long UserId;
    ///     public string UserName;
    ///     public long Score;
    ///     public long Rank;
    ///     public long BadgesGold;
    ///     public long BadgesSilver;
    ///     public long BadgesBronze;
    /// }    
    /// 
    /// </summary>
    public class VersionedSQLiteOrmLayer
    {

        ///<summary>
        /// TODO: need to provide facility to initialize these values
        ///</summary>
        public const string DataFileNameFormatPrefix = "data_files_";
        ///<summary>
        /// TODO: need to provide facility to initialize these values
        ///</summary>
        public const string DataFileNameFormat = DataFileNameFormatPrefix + "{0}_v{1}.db";

        ///<summary>
        ///</summary>
        public bool SchemaModified
        {
            get
            {
                return _schemaModified;
            }
        }

        ///<summary>
        ///</summary>
        ///<param name="dataDirectory"></param>
        ///<param name="types"></param>
        public VersionedSQLiteOrmLayer(string dataDirectory, IEnumerable<Type> types)
        {
            _types = types;
            _dataDirectory = dataDirectory;
            _schemaModified = EnsureDatabase(0, 0);
            BuildDataAdapters(types);
        }

        #region CRUD

        ///<summary>
        ///</summary>
        ///<param name="index"></param>
        ///<param name="version"></param>
        ///<typeparam name="T"></typeparam>
        ///<returns></returns>
        public List<T> SelectObjects<T>(int index, int version) where T : new()
        {
            List<T> result = new List<T>();
            using (SQLiteConnection con = CreateConnection(index, version))
            {
                using (SQLiteCommand cmd = (SQLiteCommand)_dataAdapters[typeof(T)].SelectCommand.Clone())
                {
                    cmd.Connection = con;
                    cmd.Connection.Open();
                    SQLiteDataReader rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        result.Add(BuildObject<T>(rdr));
                    }
                }
            }
            return result;
        }

        ///<summary>
        ///</summary>
        ///<param name="id"></param>
        ///<param name="index"></param>
        ///<param name="version"></param>
        ///<typeparam name="T"></typeparam>
        ///<returns></returns>
        public T SelectObject<T>(long id, int index, int version) where T : new()
        {
            using (SQLiteConnection con = CreateConnection(index, version))
            {
                using (SQLiteCommand cmd = (SQLiteCommand)_dataAdapters[typeof(T)].SelectCommand.Clone())
                {
                    string pkFieldName = GetIdFieldName<T>();
                    cmd.Parameters.AddWithValue("@" + pkFieldName, id);
                    cmd.CommandText += " WHERE " + pkFieldName + "=@" + pkFieldName;
                    cmd.Connection = con;
                    cmd.Connection.Open();
                    SQLiteDataReader rdr = cmd.ExecuteReader();
                    if (rdr.Read())
                    {
                        return BuildObject<T>(rdr);
                    }
                }
            }
            return default(T);
        }

        ///<summary>
        ///</summary>
        ///<param name="dto"></param>
        ///<param name="index"></param>
        ///<param name="version"></param>
        ///<typeparam name="T"></typeparam>
        ///<returns></returns>
        public T InsertObject<T>(T dto, int index, int version)
        {
            using (SQLiteConnection con = CreateConnection(index, version))
            {
                using (SQLiteCommand cmd = (SQLiteCommand)_dataAdapters[typeof(T)].InsertCommand.Clone())
                {
                    SetCommandParameters(cmd, dto);
                    cmd.Connection = con;
                    cmd.Connection.Open();
                    object id = cmd.ExecuteScalar();
                    // set the new id
                    typeof(T).GetField(GetIdFieldName<T>()).SetValue(dto, id);
                }
            }
            return dto;
        }

        ///<summary>
        ///</summary>
        ///<param name="dtos"></param>
        ///<param name="index"></param>
        ///<param name="version"></param>
        ///<typeparam name="T"></typeparam>
        ///<returns></returns>
        public int InsertObjects<T>(IEnumerable<T> dtos, int index, int version)
        {
            int insertCount = 0;
            using (SQLiteConnection con = CreateConnection(index, version))
            {
                con.Open();
                using (var tx = con.BeginTransaction())
                {
                    using (SQLiteCommand cmd = (SQLiteCommand)_dataAdapters[typeof(T)].InsertCommand.Clone())
                    {
                        cmd.Connection = con;
                        foreach (var dto in dtos)
                        {
                            SetCommandParameters(cmd, dto);
                            object id = cmd.ExecuteScalar();
                            // set the new id
                            typeof(T).GetField(GetIdFieldName<T>()).SetValue(dto, id);
                            insertCount++;
                        }
                    }
                    tx.Commit();
                }
            }
            return insertCount;
        }


        ///<summary>
        ///</summary>
        ///<param name="dto"></param>
        ///<param name="index"></param>
        ///<param name="version"></param>
        ///<typeparam name="T"></typeparam>
        ///<returns></returns>
        public int UpdateObject<T>(T dto, int index, int version)
        {
            using (SQLiteConnection con = CreateConnection(index, version))
            {
                using (SQLiteCommand cmd = (SQLiteCommand)_dataAdapters[typeof(T)].UpdateCommand.Clone())
                {
                    SetCommandParameters(cmd, dto);
                    cmd.Connection = con;
                    cmd.Connection.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        ///<summary>
        ///</summary>
        ///<param name="dtos"></param>
        ///<param name="index"></param>
        ///<param name="version"></param>
        ///<typeparam name="T"></typeparam>
        ///<returns></returns>
        public int UpdateObjects<T>(IEnumerable<T> dtos, int index, int version)
        {
            int updated = 0;
            using (SQLiteConnection con = CreateConnection(index, version))
            {
                con.Open();

                using (SQLiteTransaction tx = con.BeginTransaction())
                {
                    using (SQLiteCommand cmd = (SQLiteCommand)_dataAdapters[typeof(T)].UpdateCommand.Clone())
                    {
                        cmd.Connection = con;
                        foreach (T dto in dtos)
                        {
                            SetCommandParameters(cmd, dto);
                            updated += cmd.ExecuteNonQuery();
                        }
                    }

                    tx.Commit();
                }
            }
            return updated;
        }


        ///<summary>
        ///</summary>
        ///<param name="id"></param>
        ///<param name="index"></param>
        ///<param name="version"></param>
        ///<typeparam name="T"></typeparam>
        ///<returns></returns>
        public int DeleteObject<T>(long id, int index, int version) where T : new()
        {
            using (SQLiteConnection con = CreateConnection(index, version))
            {
                using (SQLiteCommand cmd = (SQLiteCommand)_dataAdapters[typeof(T)].DeleteCommand.Clone())
                {
                    // the delete adapter has only one parameter, the 'Original_' PK.
                    // of course this is only true if all PK are single columnn - in
                    // this controlled scenario this stipulation is acceptable.
                    cmd.Parameters[0].Value = id;
                    cmd.Connection = con;
                    cmd.Connection.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        #endregion


        #region Private Implementation


        private readonly IEnumerable<Type> _types;
        private readonly bool _schemaModified;
        private readonly string _dataDirectory;
        private Dictionary<Type, SQLiteDataAdapter> _dataAdapters;


        private bool EnsureDatabase(int index, int version)
        {
            if (!File.Exists(BuildFilePath(0, 0)))
            {
                foreach (string file in Directory.GetFiles(_dataDirectory, DataFileNameFormatPrefix + "*"))
                {
                    File.Delete(file);
                }
            }


            if (File.Exists(BuildFilePath(index, version)))
            {
                return false;
            }

            using (SQLiteConnection con = CreateConnection(index, version, false))
            {
                con.Open();

                foreach (Type type in _types)
                {
                    string pkField = GetIdFieldName(type);
                    bool isAutoIncrement = pkField == "RowID";
                    List<string> columns = new List<string>();
                    foreach (FieldInfo field in type.GetFields())
                    {
                        string dataType = MapClrTypeToSqliteType(field);

                        bool isPk = field.Name == pkField;
                        string columnDef = string.Format("[{0}] {1} {2} {3} NOT NULL", field.Name, dataType,
                                                         isPk ? " PRIMARY KEY " : "",
                                                         isPk && isAutoIncrement ? " AUTOINCREMENT " : "");
                        columns.Add(columnDef);
                    }

                    string sql = string.Format("DROP TABLE IF EXISTS [{0}]; CREATE TABLE [{0}] ({1});", type.Name, string.Join(",", columns.ToArray()));

                    sql = Regex.Replace(sql, @"\s+", " ").Trim();


                    using (SQLiteCommand cmd = con.CreateCommand())
                    {

                        // trying in place upgrade but seems problematic.


                        //cmd.CommandText = "SELECT sql FROM sqlite_master WHERE type='table' and name=@name";
                        //cmd.Parameters.AddWithValue("@name", type.Name);
                        //string existingSql = cmd.ExecuteScalar() as string;

                        //if (string.IsNullOrEmpty(existingSql) || sql != existingSql)
                        //{
                        //    cmd.Parameters.Clear();
                        //    cmd.CommandText = sql;
                        //    cmd.ExecuteNonQuery();
                        //}

                        
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                    }
                }
            }

            return true;
        }

        private string BuildFilePath(int index, int version)
        {
            return Path.Combine(_dataDirectory, string.Format(DataFileNameFormat, index, version));
        }

        /// <summary>
        /// This is the only place a connection should be created
        /// </summary>
        /// <param name="index"></param>
        /// <param name="version"></param>
        /// <returns></returns>
        private SQLiteConnection CreateConnection(int index, int version)
        {
            return CreateConnection(index, version, true);
        }

        /// <summary>
        /// This is the only place a connection should be created
        /// </summary>
        /// <param name="index"></param>
        /// <param name="version"></param>
        /// <param name="ensureDatabase"></param>
        /// <returns></returns>
        private SQLiteConnection CreateConnection(int index, int version, bool ensureDatabase)
        {
            if (ensureDatabase)
            {
                EnsureDatabase(index, version);
            }


            SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};", BuildFilePath(index, version)));

            return conn;
        }

        private void BuildDataAdapters(IEnumerable<Type> types)
        {
            _dataAdapters = new Dictionary<Type, SQLiteDataAdapter>();

            foreach (Type dtoType in types)
            {
                SQLiteDataAdapter dataAdapter
                    = new SQLiteDataAdapter("SELECT * FROM " + dtoType.Name, CreateConnection(0, 0));

                SQLiteCommandBuilder cmdBuilder = new SQLiteCommandBuilder(dataAdapter)
                {
                    ConflictOption = ConflictOption.OverwriteChanges
                };
                dataAdapter.InsertCommand = cmdBuilder.GetInsertCommand(true);
                dataAdapter.InsertCommand.CommandText += ";SELECT last_insert_rowid() AS ID;";
                dataAdapter.DeleteCommand = cmdBuilder.GetDeleteCommand(true);
                dataAdapter.UpdateCommand = cmdBuilder.GetUpdateCommand(true);
                _dataAdapters.Add(dtoType, dataAdapter);
            }
        }

        private static string MapClrTypeToSqliteType(FieldInfo field)
        {
            string dataType;
            if (field.FieldType == typeof(string))
            {
                dataType = "nvarchar";
            }
            else if (field.FieldType == typeof(long))
            {
                dataType = "integer";
            }
            else if (field.FieldType == typeof(bool))
            {
                dataType = "bit";
            }
            else if (field.FieldType == typeof(DateTime))
            {
                dataType = "datetime";
            }
            else
            {
                throw new Exception(String.Format("{0} is not supported", field.FieldType.Name));
            }
            return dataType;
        }

        private static void SetCommandParameters(SQLiteCommand cmd, object dto)
        {
            foreach (FieldInfo field in dto.GetType().GetFields())
            {
                object value = field.GetValue(dto);

                if (cmd.Parameters.Contains("@" + field.Name))
                {
                    cmd.Parameters["@" + field.Name].Value = value;
                }

                if (cmd.Parameters.Contains("@Original_" + field.Name))
                {
                    cmd.Parameters["@Original_" + field.Name].Value = value;
                }
            }
        }

        private static T BuildObject<T>(IDataRecord row) where T : new()
        {
            T dto = new T();
            foreach (FieldInfo field in dto.GetType().GetFields())
            {
                try
                {
                    field.SetValue(dto, row.GetValue(row.GetOrdinal(field.Name)));
                }
                // ReSharper disable EmptyGeneralCatchClause
                catch
                // ReSharper restore EmptyGeneralCatchClause
                {
                    // if the record doesnt have a column for a field, just ignore
                }
            }
            return dto;
        }

        private static string GetIdFieldName<T>()
        {
            return GetIdFieldName(typeof(T));
        }

        private static string GetIdFieldName(ICustomAttributeProvider type)
        {
            string idName =
                ((DescriptionAttribute)type.GetCustomAttributes(typeof(DescriptionAttribute), false)[0]).Description;
            return idName;
        }

        #endregion

    }
}